﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace Auditoria
{
    public class Conexion
    {
        private String str_Conexion;
        private OracleConnection conector;
        private String dbname;
        private String usuario;
        private String contraseña;
        private const String sqlTablespace = "select tablespace_name from dba_tablespaces";
        private const String sqlUsers = "select username from dba_users";
        private const String sqlRoles = "select role from dba_roles";

        public Conexion() 
        {
            this.str_Conexion = "";
            this.conector = null;
            this.dbname = "";
            this.usuario = "";
            this.contraseña = "";
        }

        public Conexion(String DBNAME, String USERNAME, String PASSWORD) 
        {
            this.conector = new OracleConnection();
            this.dbname = DBNAME;
            this.usuario = USERNAME;
            this.contraseña = PASSWORD;
            this.GenerarString();
        }

        private void GenerarString() 
        {
            if (this.usuario.ToLower().Equals("sys"))
                this.str_Conexion = "User Id=" + this.usuario + "; Password=" + this.contraseña + "; "
                        + "DBA PRIVILEGE=SYSDBA; Data Source="+this.dbname;
            else
                this.str_Conexion = "User Id=" + this.usuario + "; Password=" + this.contraseña + "; "
                        + "Data Source=" + this.dbname;
        }

        private int Conectar() 
        {
            try
            {
                this.conector.ConnectionString = this.str_Conexion;
                this.conector.Open();
                return 0;
            }
            catch (OracleException ex)
            {
                return ex.Number;
            }
            catch (Exception ex) 
            {
                Console.WriteLine(ex.Message);
                return -1;
            }
        }

        public void Desconectar() 
        {
            this.conector.Close();
        }

        public int EstaConecto() 
        {
            return this.Conectar();
        }

        public List<String> todos_tablespace() 
        {
            List<String> lista = new List<string>();
            lista.Add("");
            try
            {
                OracleCommand comando = new OracleCommand(sqlTablespace);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read()) 
                {
                    String dato = odr.GetValue(0).ToString().Trim()+"";
                    lista.Add(dato);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex) 
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> tablas_Por_Tablespace(String tablespace_name) 
        {
            List<String> lista = new List<string>();
            lista.Add("");
            String sqlTables = "select table_name from all_tables where tablespace_name like '" + tablespace_name + "'";
            try
            {
                OracleCommand comando = new OracleCommand(sqlTables);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    lista.Add(dato);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> todos_Users()
        {
            List<String> lista = new List<string>();
            lista.Add("");
            try
            {
                OracleCommand comando = new OracleCommand(sqlUsers);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    lista.Add(dato);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> todos_Roles()
        {
            List<String> lista = new List<string>();
            lista.Add("");
            try
            {
                OracleCommand comando = new OracleCommand(sqlRoles);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    lista.Add(dato);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> SysPrivs_Rol(String condicion)
        {
            List<String> lista = new List<string>();
            lista.Add("");
            String sqlTables = "select grantee, privilege from dba_sys_privs where grantee = '"+condicion+"'";
            try
            {
                OracleCommand comando = new OracleCommand(sqlTables);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    String dato2 = odr.GetValue(1).ToString().Trim() + "";
                    lista.Add(dato);
                    lista.Add(dato2);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> SysPrivs_Usr(String condicion)
        {
            List<String> lista = new List<string>();
            lista.Add("");
            String sqlTables = "select grantee, privilege from dba_sys_privs where grantee = '" + condicion + "'";
            try
            {
                OracleCommand comando = new OracleCommand(sqlTables);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    String dato2 = odr.GetValue(1).ToString().Trim() + "";
                    lista.Add(dato);
                    lista.Add(dato2);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> ObjPrivs_Rol(String condicion)
        {
            List<String> lista = new List<string>();
            lista.Add("");
            String sqlTables = "select grantee, table_name, privilege from dba_tab_privs where grantee = '"+condicion+"'";
            try
            {
                OracleCommand comando = new OracleCommand(sqlTables);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    String dato2 = odr.GetValue(1).ToString().Trim() + "";
                    String dato3 = odr.GetValue(2).ToString().Trim() + "";
                    lista.Add(dato);
                    lista.Add(dato2);
                    lista.Add(dato3);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> ObjPrivs_Usr(String condicion)
        {
            List<String> lista = new List<string>();
            lista.Add("");
            String sqlTables = "select grantee, table_name, privilege from dba_tab_privs where grantee = '" + condicion + "'";
            try
            {
                OracleCommand comando = new OracleCommand(sqlTables);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    String dato2 = odr.GetValue(1).ToString().Trim() + "";
                    String dato3 = odr.GetValue(2).ToString().Trim() + "";
                    lista.Add(dato);
                    lista.Add(dato2);
                    lista.Add(dato3);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> Privs_Tablas(String table_name)
        {
            List<String> lista = new List<string>();
            lista.Add("");
            String sqlTables = "select table_name, privilege, grantee, owner from dba_tab_privs where table_name = '" + table_name + "'";

            try
            {
                OracleCommand comando = new OracleCommand(sqlTables);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    String dato2 = odr.GetValue(1).ToString().Trim() + "";
                    String dato3 = odr.GetValue(2).ToString().Trim() + "";
                    String dato4 = odr.GetValue(3).ToString().Trim() + "";
                    lista.Add(dato);
                    lista.Add(dato2);
                    lista.Add(dato3);
                    lista.Add(dato4);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> tablas_logminer(String archive_log, String condicion,List<String> logs) 
        {
            List<String> lista = new List<string>();
            lista.Add("");


            
            String sqlProcedure1;
            OracleCommand comando1;
            this.conector.Open();
            sqlProcedure1 = "BEGIN SYS.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'" + logs[0] + "', OPTIONS =>SYS.DBMS_LOGMNR.NEW); END;";
            comando1 = new OracleCommand(sqlProcedure1);
            comando1.Connection = this.conector;
            comando1.ExecuteNonQuery();
            for (int i = 1; i < logs.Count; i++)
            {
                sqlProcedure1 = "BEGIN SYS.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME =>'" + logs[i] + "',OPTIONS => DBMS_LOGMNR.ADDFILE); END;";
                comando1 = new OracleCommand(sqlProcedure1);
                comando1.Connection = this.conector;
                comando1.ExecuteNonQuery();
            }
            String sqlProcedure2 = "BEGIN SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS =>SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); END;";
            String sqlseleclogminer = "select timestamp, username, table_name, operation, sql_undo, sql_redo from v$logmnr_contents where username like '" + condicion + "' or table_name like '"+condicion+"'";
            try
            {
                //OracleCommand comando1 = new OracleCommand(sqlProcedure1);
                OracleCommand comando2 = new OracleCommand(sqlProcedure2);
                OracleCommand comando3 = new OracleCommand(sqlseleclogminer);
                //comando1.Connection = this.conector;
                comando2.Connection = this.conector;
                comando3.Connection = this.conector;
//                this.conector.Open();
               // comando1.ExecuteNonQuery();
                comando2.ExecuteNonQuery();
                OracleDataReader odr = comando3.ExecuteReader();
                while (odr.Read())
                {
                    lista.Add(odr.GetValue(0).ToString().Trim() + "");
                    lista.Add(odr.GetValue(1).ToString().Trim() + "");
                    lista.Add(odr.GetValue(2).ToString().Trim() + "");
                    lista.Add(odr.GetValue(3).ToString().Trim() + "");
                    lista.Add(odr.GetValue(4).ToString().Trim() + "");
                    lista.Add(odr.GetValue(5).ToString().Trim() + "");
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

        public List<String> todos_logs_offline() 
        {
            List<String> lista = new List<string>();
            lista.Add("");
            String sqlLogs = "select sequence#, name, first_time from v$archived_log order by first_time";
            try
            {
                OracleCommand comando = new OracleCommand(sqlLogs);
                comando.Connection = this.conector;
                this.conector.Open();
                OracleDataReader odr = comando.ExecuteReader();
                while (odr.Read())
                {
                    String dato = odr.GetValue(0).ToString().Trim() + "";
                    String dato2 = odr.GetValue(1).ToString().Trim() + "";
                    String dato3 = odr.GetValue(2).ToString().Trim() + "";
                    lista.Add(dato);
                    lista.Add(dato2);
                    lista.Add(dato3);
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            this.Desconectar();
            return lista;
        }

    }
}
